const db = require('../db');
const bcrypt = require('bcrypt');

class AdminModel {
    // 根据用户名查找管理员
    static async findByUsername(username) {
        try {
            const [rows] = await db.promise().query(
                'SELECT * FROM admin WHERE username = ? AND status = 1',
                [username]
            );
            return rows[0];
        } catch (error) {
            console.error('查找管理员失败:', error);
            throw error;
        }
    }
    // 修改密码
    static async changePassword(id, oldPassword, newPassword) {
        const hashedPassword = await bcrypt.hash(newPassword, 10);
        await db.promise().query('UPDATE admin SET password = ? WHERE id = ?', [hashedPassword, id]);
    }

    // 更新最后登录时间
    static async updateLoginTime(id) {
        try {
            await db.promise().query(
                'UPDATE admin SET last_login_time = NOW() WHERE id = ?',
                [id]
            );
        } catch (error) {
            console.error('更新登录时间失败:', error);
            throw error;
        }
    }

    // 获取所有用户
    static async getAllUsers() {
        try {
            // 检查表是否存在
            console.log("我的wowe ");

            const [rows] = await db.promise().query('SELECT * FROM users');

            console.log('查询到的用:', rows);
            return rows;
        } catch (error) {
            console.error('获取用户列表失败:', error);
            throw error;
        }
    }
    // 删除用户
    static async deleteUser(userId) {
        await db.promise().query('DELETE FROM users WHERE user_id = ?', [userId]);
    }
    // 更新用户
    static async updateUser(userId, updateData) {
        console.log(userId, updateData);

        await db.promise().query('UPDATE users SET ? WHERE user_id = ?', [updateData, userId]);
    }
    // 获取所有商品
    static async getAllProducts() {
        const [rows] = await db.promise().query('SELECT * FROM products');
        return rows;
    }

    // 创建管理员
    static async createAdmin(adminData) {
        console.log("改改 ");
        try {
            // 生成密码哈希
            const salt = await bcrypt.genSalt(10);
            const hashedPassword = await bcrypt.hash(adminData.password, salt);

            const [result] = await db.promise().query(
                `INSERT INTO admin (
                    username, 
                    password, 
                    nickname,
                    avatar,
                    status
                ) VALUES (?, ?, ?, ?, ?)`,
                [
                    adminData.username,
                    hashedPassword,
                    adminData.nickname,
                    adminData.avatar || null,
                    adminData.status || 1
                ]
            );

            return result.insertId;
        } catch (error) {
            console.error('创建管理员失败:', error);
            throw error;
        }
    }

    // 检查用名是否存在
    static async checkUsernameExists(username) {
        try {
            const [rows] = await db.promise().query(
                'SELECT id FROM admin WHERE username = ?',
                [username]
            );
            return rows.length > 0;
        } catch (error) {
            console.error('检查用户名失败:', error);
            throw error;
        }
    }

    // 获取所有订单
    static async getAllOrders() {
        console.log("s订单；表");

        try {
            // 先检查表是否存在
            const [tables] = await db.promise().query(`
                SHOW TABLES LIKE 'orders'
            `);

            if (tables.length === 0) {
                console.log('orders表不存在');
                return [];
            }

            const [rows] = await db.promise().query(`
                SELECT 
                    o.order_id,
                    o.order_status,
                    o.total_price,
                    o.shipping_address,
                    o.created_at,
                    o.updated_at,
                    u.username as user_name,
                    GROUP_CONCAT(p.name) as product_names,
                    GROUP_CONCAT(p.image_url) as product_images,
                    GROUP_CONCAT(oi.quantity) as quantities,
                    GROUP_CONCAT(oi.price) as prices
                FROM orders o
                LEFT JOIN users u ON o.user_id = u.user_id
                LEFT JOIN order_items oi ON o.order_id = oi.order_id
                LEFT JOIN products p ON oi.product_id = p.product_id
                GROUP BY o.order_id
                ORDER BY o.created_at DESC
            `);

            // 处理数据
            const processedOrders = rows.map(order => {
                // 处理多个商品的情况
                const productNames = order.product_names ? order.product_names.split(',') : [];
                const productImages = order.product_images ? order.product_images.split(',').map(img => {
                    try {
                        // 打印原始图片数据以便调试
                        console.log('原始图片数据:', img);

                        if (!img) return null;

                        // 如果是JSON字符串数组
                        if (img.includes('[')) {
                            const images = JSON.parse(img);
                            console.log('解析后的图片数组:', images);
                            return images[0]; // 返回第一张图片
                        }

                        // 如果已经是单图片路径
                        return img;
                    } catch (e) {
                        console.log('图片处理错误:', e, '原始数据:', img);
                        return img; // 如果解析失败，返回原始数据
                    }
                }) : [];

                console.log('处理后的图片:', productImages[0]);

                const quantities = order.quantities ? order.quantities.split(',').map(Number) : [];
                const prices = order.prices ? order.prices.split(',').map(Number) : [];

                return {
                    ...order,
                    product_name: productNames[0], // 使用第一个商品名称
                    product_image: productImages[0], // 使用第一个商品图片
                    quantity: quantities[0], // 使用第一个数量
                    price: prices[0], // 使用第一个价格
                    total_amount: Number(order.total_price || 0),
                    status_text: AdminModel.getOrderStatusText(order.order_status)
                };
            });

            console.log('原始订单数据:', rows);
            console.log('处理后的订单数据:', processedOrders);

            return rows;
        } catch (error) {
            console.error('获取订单列表失败:', error);
            throw error;
        }
    }

    // 更新订单状态
    static async updateOrderStatus(orderId, status) {
        try {
            await db.promise().query(
                'UPDATE orders SET order_status = ? WHERE order_id = ?',
                [status, orderId]
            );
        } catch (error) {
            console.error('更新订单状态失败:', error);
            throw error;
        }
    }
    // 更新商品
    static async updateProduct(productId, updateData) {
        await db.promise().query('UPDATE products SET ? WHERE product_id = ?', [updateData, productId]);
    }

    // 删除订单
    static async deleteOrder(orderId) {
        try {
            await db.promise().query(
                'DELETE FROM orders WHERE order_id = ?',
                [orderId]
            );
        } catch (error) {
            console.error('删除订单失败:', error);
            throw error;
        }
    }

    // 获取订单状态文本
    static getOrderStatusText(status) {
        const statusMap = {
            'pending': '待付款',
            'confirmed': '待发货',
            'shipped': '已发货',
            'delivered': '已完成',
            'cancelled': '已取消'
        };
        return statusMap[status] || status;
    }

    // 获取最近订单
    static async getRecentOrders(limit = 5) {
        try {
            console.log('开始获取最近订单');

            // 先查表是否存在
            const [tables] = await db.promise().query(`
                SHOW TABLES LIKE 'orders'
            `);

            if (tables.length === 0) {
                console.log('orders表不存在');
                return [];
            }

            // 检查是否有订单数据
            const [countResult] = await db.promise().query('SELECT COUNT(*) as count FROM orders');
            console.log('订单总数:', countResult[0].count);

            // 修正SQL查询
            const [rows] = await db.promise().query(`
               SELECT 
    o.order_id,
    o.order_status,
    o.total_price,
    o.created_at,
    o.shipping_address,      
    os.recipient_phone,      
    u.username AS customer_name,
    GROUP_CONCAT(p.name) AS product_names,
    GROUP_CONCAT(oi.quantity) AS quantities,
    GROUP_CONCAT(oi.price) AS prices
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
LEFT JOIN order_shipping os ON o.order_id = os.order_id  
GROUP BY 
    o.order_id, 
    o.order_status, 
    o.total_price, 
    o.created_at, 
    o.shipping_address,      
    os.recipient_phone,      
    u.username
ORDER BY o.created_at DESC
LIMIT ?
        `, [limit]);

            console.log('查询到的原始订单数据:', rows);

            const processedOrders = rows.map(order => ({
                id: order.order_id,
                orderNo: `ORD${order.created_at.getTime()}`,
                customer: order.customer_name || '未知用户',
                amount: Number(order.total_price || 0),
                status: order.order_status || 'pending',
                phone: order.recipient_phone || '未知手机号',
                address: order.shipping_address || '未知地址',
                products: order.product_names?.split(',') || [],
                created_at: order.created_at
            }));

            console.log('处理后的订单数据:', processedOrders);
            return processedOrders;
        } catch (error) {
            console.error('获取最近订单失败:', error);
            throw error;
        }
    }

    // 获取热销商品
    static async getHotProducts(limit = 5) {
        try {
            console.log('开始获取热销商品');

            // 先检查表是否存在
            const [tables] = await db.promise().query(`
                SHOW TABLES LIKE 'products'
            `);

            if (tables.length === 0) {
                console.log('products表不存在');
                return [];
            }

            // 检查是否有商品数据
            const [countResult] = await db.promise().query('SELECT COUNT(*) as count FROM products');
            console.log('商品数:', countResult[0].count);

            // 修改查询，即使没有订单也返回商品
            const [rows] = await db.promise().query(`
                SELECT 
                    p.product_id,
                    p.name,
                    p.price,
                    p.image_url,
                    COALESCE(COUNT(oi.order_item_id), 0) as sales_count
                FROM products p
                LEFT JOIN order_items oi ON p.product_id = oi.product_id
                GROUP BY p.product_id, p.name, p.price, p.image_url
                ORDER BY sales_count DESC, p.created_at DESC
                LIMIT ?
            `, [limit]);

            console.log('查询到的原始商品数据:', rows);

            const processedProducts = rows.map(product => {
                let imageUrl = null;
                try {
                    if (product.image_url) {
                        const images = JSON.parse(product.image_url);
                        imageUrl = images[0];
                    }
                } catch (e) {
                    console.error('解析图片URL失败:', e);
                    imageUrl = product.image_url;
                }

                return {
                    id: product.product_id,
                    name: product.name || '未命名商品',
                    price: Number(product.price || 0),
                    sales: Number(product.sales_count || 0),
                    image: imageUrl
                };
            });

            console.log('处理后的商品数据:', processedProducts);
            return processedProducts;
        } catch (error) {
            console.error('获取热销商品失败:', error);
            throw error;
        }
    }

    // 获取统计数据
    static async getStatistics() {
        try {
            // 获取总订单数
            const [orderRows] = await db.promise().query('SELECT COUNT(*) as total FROM orders');
            const totalOrders = orderRows[0].total;

            // 获取今日订单数
            const [todayOrderRows] = await db.promise().query(`
                SELECT COUNT(*) as total 
                FROM orders 
                WHERE DATE(created_at) = CURDATE()
            `);
            const todayOrders = todayOrderRows[0].total;

            // 获取用户总数
            const [userRows] = await db.promise().query('SELECT COUNT(*) as total FROM users');
            const totalUsers = userRows[0].total;

            // 获取商品总数
            const [productRows] = await db.promise().query('SELECT COUNT(*) as total FROM products');
            const totalProducts = productRows[0].total;

            // 获最近订单
            const [recentOrders] = await db.promise().query(`
                SELECT 
    o.order_id,
    o.order_status,
    o.total_price,
    o.created_at,
    o.shipping_address,      -- 已有的字段
    os.recipient_phone,      -- 新添加的字段，来自 order_shipping 表
    u.username AS customer_name,
    GROUP_CONCAT(p.name) AS product_names,
    GROUP_CONCAT(oi.quantity) AS quantities,
    GROUP_CONCAT(oi.price) AS prices
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
LEFT JOIN order_shipping os ON o.order_id = os.order_id  -- 关联 order_shipping 表
GROUP BY 
    o.order_id, 
    o.order_status, 
    o.total_price, 
    o.created_at, 
    o.shipping_address,      -- 确保新字段也被包含在 GROUP BY 中
    os.recipient_phone,      -- 新添加的字段
    u.username
ORDER BY o.created_at DESC
LIMIT 5;
            `);

            // 获取热销商品
            const [hotProducts] = await db.promise().query(`
                SELECT 
                    p.product_id as id,
                    p.name,
                    p.price,
                    p.image_url as image,
                    COUNT(oi.order_item_id) as sales
                FROM products p
                LEFT JOIN order_items oi ON p.product_id = oi.product_id
                GROUP BY p.product_id
                ORDER BY sales DESC
                LIMIT 5
            `);

            // 获取销售趋势
            const [salesTrend] = await db.promise().query(`
                SELECT 
                    DATE(created_at) as date,
                    COUNT(*) as order_count,
                    SUM(total_price) as sales_amount
                FROM orders
                WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
                GROUP BY DATE(created_at)
                ORDER BY date DESC
            `);

            return {
                totalOrders,
                todayOrders,
                totalUsers,
                totalProducts,
                recentOrders,
                hotProducts,
                salesTrend
            };
        } catch (error) {
            console.error('获取统计数据失败:', error);
            throw error;
        }
    }

    // 获取拼团列表
    static async getGroupBuyList() {
        try {
            const [rows] = await db.promise().query(`
                SELECT 
                    g.groupbuy_id,
                    g.group_size,
                    g.current_count,
                    g.status,
                    g.start_time,
                    g.end_time,
                    g.is_paid,
                    p.name as product_name,
                    p.price as original_price,
                    p.image_url,
                    u.username as creator_name,
                    u.user_id as creator_id
                FROM groupbuy g
                JOIN products p ON g.product_id = p.product_id
                JOIN users u ON g.creator_user_id = u.user_id
                ORDER BY g.created_at DESC
            `);

            // 获取每个拼团的参与者信息
            for (let group of rows) {
                const [participants] = await db.promise().query(`
                    SELECT u.user_id, u.username
                    FROM groupbuy_participants gp
                    JOIN users u ON gp.user_id = u.user_id
                    WHERE gp.groupbuy_id = ?
                `, [group.groupbuy_id]);

                group.members = participants;

                // 处理图片路径
                try {
                    if (group.image_url) {
                        // 尝试解析 JSON 字符串
                        const imageData = JSON.parse(group.image_url);
                        // 如果是数组，取第一个图片
                        if (Array.isArray(imageData) && imageData.length > 0) {
                            group.image_url = `http://localhost:3001${imageData[0]}`;
                        } else if (typeof imageData === 'string') {
                            // 如果是单个图片路径
                            group.image_url = `http://localhost:3001${imageData}`;
                        }
                    }
                } catch (e) {
                    console.error('解析图片URL失败:', e, group.image_url);
                    // 如果解析失败，检查是否已经是有效的路径
                    if (group.image_url && !group.image_url.startsWith('http')) {
                        group.image_url = `http://localhost:3001${group.image_url}`;
                    }
                }

                // 计算团购价格(假设是原价的8折)
                group.group_price = (parseFloat(group.original_price) * 0.8).toFixed(2);
            }

            console.log('处理后的拼团数据:', rows); // 添加日志
            return rows;
        } catch (error) {
            console.error('获取拼团列表失败:', error);
            throw error;
        }
    }
    // 删除商品
    static async deleteProduct(productId) {
        console.log('wp ' + productId);
        await db.promise().query('DELETE FROM products WHERE product_id = ?', [productId]);
    }
}

module.exports = AdminModel; 